﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;

namespace Shuba.Data
{
    public class Loan
    {
        public Loan()
        { 
        }

        public List<AppCrossReffInfo> GetList(string value)
        {
            List<AppCrossReffInfo> list = new List<AppCrossReffInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM V_APP_CROSS_REFF WHERE (CIF_NUMBER LIKE '%' + @PARAM + '%') OR (DEBITOR_NAME LIKE '%' + @PARAM + '%') ORDER BY OPEN_DATE DESC");
            cmd.CommandTimeout = 600;
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new AppCrossReffInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetInt32(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetInt16(9), dr.GetInt16(10), dr.GetString(11), dr.GetString(12), dr.GetString(13), dr.GetDateTime(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetDouble(18)));
                }
                dr.Close();
            }
            return list;
        }

        public List<AppCrossReffInfo> GetListByBranch(string BranchID)
        {
            List<AppCrossReffInfo> list = new List<AppCrossReffInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM V_APP_CROSS_REFF WHERE UNIT_CODE LIKE '' + @BRANCH_ID + '' ORDER BY OPEN_DATE DESC");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BranchID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new AppCrossReffInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetInt32(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetInt16(9), dr.GetInt16(10), dr.GetString(11), dr.GetString(12), dr.GetString(13), dr.GetDateTime(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetDouble(18)));
                }
                dr.Close();
            }
            return list;
        }

        public List<LoanInfo> GetListApproval(string value, string UpdateBy)
        {
            List<LoanInfo> list = new List<LoanInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_LOAN_TMP WHERE ((CIF_NUMBER LIKE '%' + @PARAM + '%') OR (LOAN_NUMBER LIKE '%' + @PARAM + '%') OR (APP_REF_NO LIKE '%' + @PARAM + '%')) AND (RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '') ORDER BY UPDATE_DATE DESC");
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new LoanInfo(dr.GetInt32(0), dr.GetString(1), dr.GetString(2), dr.GetInt16(3), dr.GetString(4), dr.GetDateTime(5), dr.GetDouble(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10), dr.GetString(11)));
                }
                dr.Close();
            }
            return list;
        }

        public LoanInfo Get(string CIF_NUMBER, string LOAN_NUMBER)
        {
            LoanInfo ci = new LoanInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_LOAN WHERE LTRIM(RTRIM(CIF_NUMBER)) LIKE '' + @CIF_NUMBER + '' AND LTRIM(RTRIM(LOAN_NUMBER)) LIKE '' + @LOAN_NUMBER + ''");
            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new LoanInfo(dr.GetInt32(0), dr.GetString(1), dr.GetString(2), dr.GetInt16(3), dr.GetString(4), dr.GetDateTime(5), dr.GetDouble(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10));
                }
                dr.Close();
            }
            return ci;
        }

        public LoanInfo GetApprovalByID(string CIF_NUMBER, string LOAN_NUMBER)
        {
            LoanInfo ci = new LoanInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_LOAN_TMP WHERE LTRIM(RTRIM(CIF_NUMBER)) LIKE '' + @CIF_NUMBER + '' AND LTRIM(RTRIM(LOAN_NUMBER)) LIKE '' + @LOAN_NUMBER + ''");
            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new LoanInfo(dr.GetInt32(0), dr.GetString(1), dr.GetString(2), dr.GetInt16(3), dr.GetString(4), dr.GetDateTime(5), dr.GetDouble(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10), dr.GetString(11));
                }
                dr.Close();
            }
            return ci;
        }

        public void Save(LoanInfo TBL_LOAN)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_LOAN_TMP
                                SET 
                                APP_REF_NO = LEFT(@APP_REF_NO,19)
                                ,PRODUCT_CODE = @PRODUCT_CODE
                                ,LINE_NUMBER = LEFT(@LINE_NUMBER,12)
                                ,OPEN_DATE = @OPEN_DATE
                                ,DISBURSE_AMOUNT = @DISBURSE_AMOUNT
                                ,ENTRY_BY = @ENTRY_BY
                                ,ENTRY_DATE = @ENTRY_DATE
                                ,UPDATE_BY = @UPDATE_BY
                                ,UPDATE_DATE = @UPDATE_DATE
                                WHERE (CIF_NUMBER = @CIF_NUMBER) AND (LOAN_NUMBER = @LOAN_NUMBER);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_LOAN_TMP
                                (
                                    CIF_NUMBER,
                                    LOAN_NUMBER,
                                    APP_REF_NO,
                                    PRODUCT_CODE,
                                    LINE_NUMBER,
                                    OPEN_DATE,
                                    DISBURSE_AMOUNT,
                                    ENTRY_BY,
                                    ENTRY_DATE,
                                    UPDATE_BY,
                                    UPDATE_DATE,
                                    ACT_TYPE
                                ) 
                                VALUES
                                (
                                    @CIF_NUMBER
                                    ,LEFT(@LOAN_NUMBER,16)
                                    ,LEFT(@APP_REF_NO,19)
                                    ,@PRODUCT_CODE
                                    ,LEFT(@LINE_NUMBER,12)
                                    ,@OPEN_DATE
                                    ,@DISBURSE_AMOUNT
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@ACT_TYPE
                                );
                            ");
            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int32, TBL_LOAN.CIF_NUMBER);
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, TBL_LOAN.LOAN_NUMBER);
            db.AddInParameter(cmd, "@APP_REF_NO", DbType.String, TBL_LOAN.APP_REF_NO);
            db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.Int16, TBL_LOAN.PRODUCT_CODE);
            db.AddInParameter(cmd, "@LINE_NUMBER", DbType.String, TBL_LOAN.LINE_NUMBER);
            db.AddInParameter(cmd, "@OPEN_DATE", DbType.DateTime, TBL_LOAN.OPEN_DATE);
            db.AddInParameter(cmd, "@DISBURSE_AMOUNT", DbType.Double, TBL_LOAN.DISBURSE_AMOUNT);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, TBL_LOAN.ENTRY_BY);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, TBL_LOAN.ENTRY_DATE);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_LOAN.UPDATE_BY);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_LOAN.UPDATE_DATE);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, TBL_LOAN.ACT_TYPE); 
            db.ExecuteNonQuery(cmd);
        }

        public int Approve(string CIF_NUMBER, string LOAN_NUMBER, string ActType)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();
                DbCommand cmd = db.GetStoredProcCommand("LOAN_APPROVAL");

                db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
                db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);

                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return status;
        }

        public int Reject(string CIF_NUMBER, string LOAN_NUMBER)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                string sql;
                DbCommand cmd;

                sql = @"
                DELETE FROM TBL_LOAN_TMP
                WHERE (LTRIM(RTRIM(CIF_NUMBER)) LIKE '' + @CIF_NUMBER + '') AND (LTRIM(RTRIM(LOAN_NUMBER)) LIKE '' + @LOAN_NUMBER + '');
                ";
                cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
                db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return status;
        }

        private bool CharToBool(string source)
        {
            if (source == "0")
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        private string BoolToChar(bool source)
        {
            if (source)
            {
                return "1";
            }
            else
            {
                return "0";
            }
        }
    }
 }

